package cjh;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

import model.AdminVO;
import model.DepartVO;
import model.ProfessorVO;
import model.StudentVO;
import model.SubjectVO;

import dao.JDBC_SQL;
import dao.JDBC_ConnClose;
import screen.AdminMain;
import screen.LoginMain;

public class Admin_Service {
	static 	JDBC_SQL sql = new JDBC_SQL();
	static StringBuilder sb = new StringBuilder();
	static Scanner sc = new Scanner(System.in); 
	static boolean b = true;
	static String[] smt = { "순번", "ID", "이름", "연락처", "등록일자" };
	static Connection conn = null;
	static PreparedStatement pstmt = null;
	static ResultSet rs = null;
	static String add = null;
	static String logId = LoginMain.logIdVO;
	
	
	public static void readAdmin() {
		sb.append("\n*** 관리자 정보 검색 ***").append("\n[ 01 ] 관리자 전체 검색").append("\n[ 02 ] 관리자 아이디 검색")
		.append("\n[ 03 ] 관리자 이름 검색").append("\n[ 09 ] 로그 아웃").append("\n[ 00 ] 상위 메뉴로 이동");
		System.out.println(sb);
		while ( b ) {
			System.out.print("수행하실 업무를 선택하세요 : ");
			switch ( sc.next() ) {
			case "1" : case "01" :
				sb = new StringBuilder();
			//	AdminFindAll(); b = false; break;
			case "2" : case "02" :
				sb = new StringBuilder();
				AdminFindId(); b = false; break;
			case "3" : case "03" :
				sb = new StringBuilder();
				AdminFindName(); b = false; break;
			case "9": case "09":
				System.out.println("사용자 접속을 종료합니다. 이용해 주셔서 감사합니다.");
				sb = new StringBuilder();
				logId = null;
				b = false; break;
			case "0" : case "00" :
				System.out.println("관리자 관련 업무 화면으로 이동합니다.");
				sb = new StringBuilder();
				AdminMain.AdminSubScreen(); b = false; break;
			default :
				System.out.println("잘못 입력하셨습니다. 다시 입력해주세요");
			}
		} 
	}
	
//	public static List<AdminVO>  AdminFindAll( ) {
		public static void  AdminFindAll( ) {		
		try { 
			conn = JDBC_ConnClose.openDataBase();
			pstmt = conn.prepareStatement(sql.AdminSelectAll1SQL);
			List<AdminVO> result = new ArrayList<>();
			rs = pstmt.executeQuery();
			sb.append(String.format("%-3s", smt[0])).append(String.format("%-18s", smt[1]))
			.append(String.format("%-10s", smt[2])).append(String.format("%-18s", smt[3]))
			.append(String.format("%-20s", smt[4])).append("\n");
			int count = 1;
			while ( rs.next() ) {
			sb.append(String.format("%-3s", count++)).append(String.format("%-18s", rs.getString(1)))
			.append(String.format("%-10s", rs.getString(2))).append(String.format("%-18s", rs.getString(3)))
			.append(String.format("%-20s", rs.getString(4))).append("\n");
			}
			System.out.println( sb.toString() );
			System.out.println("계속 진행하시려면 [ ENTER KEY ] 를 입력하세요" );
			System.in.read();
			System.in.read();
			sb = new StringBuilder();
			AdminMain.AdminSelScreen();
		} catch ( Exception e ) {
			e.printStackTrace();
		} finally {
			JDBC_ConnClose.closeDataBase(conn, pstmt, rs);
		}
		// return result;
	}
	
	public static void AdminFindId() {
		System.out.print("조회하실 관리자의 아이디를 입력하세요 : ");
		add = sc.next();
		try {
			conn = JDBC_ConnClose.openDataBase();
			pstmt = conn.prepareStatement(sql.AdminSelectIdSQL);
			pstmt.setString( 1, add );
			rs = pstmt.executeQuery();
			sb.append(String.format("%-3s", smt[0])).append(String.format("%-18s", smt[1]))
			.append(String.format("%-10s", smt[2])).append(String.format("%-18s", smt[3]))
			.append(String.format("%-20s", smt[4])).append("\n");
			int count = 1;
			while ( rs.next() ) {
			sb.append(String.format("%-3s", count++)).append(String.format("%-18s", rs.getString(1)))
			.append(String.format("%-10s", rs.getString(2))).append(String.format("%-18s", rs.getString(3)))
			.append(String.format("%-20s", rs.getString(4))).append("\n");
			}
			System.out.println( sb.toString() );
			System.out.println("계속 진행하시려면 [ ENTER KEY ] 를 입력하세요" );
			System.in.read();
			System.in.read();
			sb = new StringBuilder();
			AdminMain.AdminSelScreen();
		} catch ( Exception e ) {
			e.printStackTrace();
		} finally {
			JDBC_ConnClose.closeDataBase(conn, pstmt, rs);
		}
	}
	
	public static void AdminFindName() {
		System.out.print("조회하실 관리자의 이름을 입력하세요 : ");
		add = sc.next();
		try {
			conn = JDBC_ConnClose.openDataBase();
			pstmt = conn.prepareStatement(sql.AdminSelectNameSQL);
			pstmt.setString( 1, add );
			rs = pstmt.executeQuery();
			sb.append(String.format("%-3s", smt[0])).append(String.format("%-18s", smt[1]))
			.append(String.format("%-10s", smt[2])).append(String.format("%-18s", smt[3]))
			.append(String.format("%-20s", smt[4])).append("\n");
			int count = 1;
			while ( rs.next() ) {
			sb.append(String.format("%-3s", count++)).append(String.format("%-18s", rs.getString(1)))
			.append(String.format("%-10s", rs.getString(2))).append(String.format("%-18s", rs.getString(3)))
			.append(String.format("%-20s", rs.getString(4))).append("\n");
			}
			System.out.println( sb.toString() );
			System.out.println("계속 진행하시려면 [ ENTER KEY ] 를 입력하세요" );
			System.in.read();
			System.in.read();
			sb = new StringBuilder();
			AdminMain.AdminSelScreen();
		} catch ( Exception e ) {
			e.printStackTrace();
		} finally {
			JDBC_ConnClose.closeDataBase(conn, pstmt, rs);
		}
	}
	
	public static List<AdminVO> selectMember() {
		List<AdminVO> result = new ArrayList<>();			// 동적배열
		
		try {
				conn = JDBC_ConnClose.openDataBase();
				pstmt = conn.prepareStatement(sql.AdminSelectIdSQL);
				pstmt.setString( 1, add );
				rs = pstmt.executeQuery();
				while( rs.next() ) {
					AdminVO m = new AdminVO();
					m.setAdmin_tr( rs.getString("ADMIN_TR") );
					m.setAdmin_id( rs.getString("ADMIN_ID") );
					m.setAdmin_name( rs.getString("ADMIN_NAME") );
					m.setAdmin_pw( rs.getString("ADMIN_PW") );
					m.setAdmin_tel( rs.getString("ADMIN_TEL") );
					m.setReg_date( rs.getString("regdate") );
					result.add( m );
										
				}
				
				
		} catch ( Exception ex ) {
			ex.printStackTrace();
			
		}finally {
		
			JDBC_ConnClose.closeDataBase(conn, pstmt, rs);
		}
		
		return result;
	}
	
	public static void insertAdminVO( AdminVO v ) {
		try {
			conn = JDBC_ConnClose.openDataBase();
			pstmt = conn.prepareStatement(sql.AdminInsertSQL );
			pstmt.setString(1, v.getAdmin_id());
			pstmt.setString(2, v.getAdmin_name() );
			pstmt.setString(3, v.getAdmin_pw() );
			pstmt.setString(4, v.getAdmin_tel() )
			;
			// etc 6,7,8,9
			pstmt.executeUpdate();


		} catch ( Exception e ) {
			e.printStackTrace();
		} finally {
			JDBC_ConnClose.closeDataBase(conn, pstmt, rs);
		}
		
	}
	
	public static void updateAdminVO( AdminVO v ){
	
		try {
			conn = JDBC_ConnClose.openDataBase();
			pstmt = conn.prepareStatement(sql.AdminUpdateSQL );
			
			pstmt.setString(1, v.getAdmin_name() );
			pstmt.setString(2, v.getAdmin_pw() );
			pstmt.setString(3, v.getAdmin_tel() );
			pstmt.setString(4, v.getAdmin_id());
						
			// etc 6,7,8,9
			pstmt.executeUpdate();


		} catch ( Exception e ) {
			e.printStackTrace();
		} finally {
			JDBC_ConnClose.closeDataBase(conn, pstmt, rs);
		}
		
		
		
		
		
		
	}
	
	
	public static void insertStuVO( StudentVO v ) {
		try {
			conn = JDBC_ConnClose.openDataBase();
			pstmt = conn.prepareStatement(sql.StudentInsertSQL );
			
			pstmt.setString(1, v.getStd_no());
			pstmt.setString(2, v.getStd_pw() );
			pstmt.setString(3, v.getStd_name() );
			pstmt.setString(4, v.getStd_tel() );
			pstmt.setString(5, v.getStd_addr() );
			pstmt.setString(6, v.getDept_no() );
			;
			// etc 6,7,8,9
			pstmt.executeUpdate();


		} catch ( Exception e ) {
			e.printStackTrace();
		} finally {
			JDBC_ConnClose.closeDataBase(conn, pstmt, rs);
		}
		
	}
	
	// 교수정보 수정
	public static void updateProfVO( ProfessorVO v ){
		
		try {
			conn = JDBC_ConnClose.openDataBase();
			pstmt = conn.prepareStatement(sql.ProfessorUpdateSQL );
			
			pstmt.setString(1, v.getProf_pw() );
			pstmt.setString(2, v.getProf_name() );
			pstmt.setString(3, v.getProf_tel());
			pstmt.setString(4, v.getProf_office() );
			pstmt.setString(5, v.getProf_no() );
						
			// etc 6,7,8,9
			pstmt.executeUpdate();


		} catch ( Exception e ) {
			e.printStackTrace();
		} finally {
			JDBC_ConnClose.closeDataBase(conn, pstmt, rs);
		}
		
		
		
		
		
		
	}
	
	
	
		// 학생정보 수정
		public static void updateStuVO( StudentVO v ){
		
		try {
			conn = JDBC_ConnClose.openDataBase();
			pstmt = conn.prepareStatement(sql.StudentUpdateSQL );
			
			pstmt.setString(1, v.getStd_pw() );
			pstmt.setString(2, v.getStd_name() );
			pstmt.setString(3, v.getStd_tel());
			pstmt.setString(4, v.getStd_addr() );
			pstmt.setString(5, v.getStd_no() );
						
			// etc 6,7,8,9
			pstmt.executeUpdate();


		} catch ( Exception e ) {
			e.printStackTrace();
		} finally {
			JDBC_ConnClose.closeDataBase(conn, pstmt, rs);
		}
		
		
		
		
		
		
	}
	
		// 과목정보 수정
		public static void updateSubVO( SubjectVO v ){
		
		try {
			conn = JDBC_ConnClose.openDataBase();
			pstmt = conn.prepareStatement(sql.SubjectUpdateSQL );
			
			pstmt.setString(1, v.getSub_name() );
			pstmt.setString(2, v.getSub_info() );
			pstmt.setString(3, v.getSub_point() );
			pstmt.setString(4, v.getSub_no() );
									
			// etc 6,7,8,9
			pstmt.executeUpdate();


		} catch ( Exception e ) {
			e.printStackTrace();
		} finally {
			JDBC_ConnClose.closeDataBase(conn, pstmt, rs);
		}
		
		
		
		
		
		
	}
	
		
	// 교수정보 등록
		public static void insertProfVO( ProfessorVO v ) {
			try {
				conn = JDBC_ConnClose.openDataBase();
				pstmt = conn.prepareStatement(sql.ProfessorInsertSQL );
				
				pstmt.setString(1, v.getProf_no());
				pstmt.setString(2, v.getProf_pw() );
				pstmt.setString(3, v.getProf_name() );
				pstmt.setString(4, v.getDept_no() );
				pstmt.setString(5, v.getProf_tel() );
				pstmt.setString(6, v.getProf_addr() );
				pstmt.setString(7, v.getProf_office() );
				;
				// etc 6,7,8,9
				pstmt.executeUpdate();


			} catch ( Exception e ) {
				e.printStackTrace();
			} finally {
				JDBC_ConnClose.closeDataBase(conn, pstmt, rs);
			}
			
		}
		
		// 과목정보 등록
		public static void insertSubVO( SubjectVO v ) {
			try {
				conn = JDBC_ConnClose.openDataBase();
				pstmt = conn.prepareStatement(sql.SubjectInsertSQL );
				
				pstmt.setString(1, v.getSub_no());
				pstmt.setString(2, v.getSub_name() );
				pstmt.setString(3, v.getSub_type_no() );
				pstmt.setString(4, v.getSub_info() );
				pstmt.setString(5, v.getSub_point() );
				pstmt.setString(6, v.getProf_no() );
				
				
				// etc 6,7,8,9
				pstmt.executeUpdate();


			} catch ( Exception e ) {
				e.printStackTrace();
			} finally {
				JDBC_ConnClose.closeDataBase(conn, pstmt, rs);
			}
			
		}
		
		
	
} // class